<?php

/**
 * 数据库类，关于oralce部分
 *
 * @param	array   $options	数据库连接的配置参数
 *
 * @return   查询数据库类是否连接成功
 */
class database_oracle {
	public $errID;
	public $count;
	private $debug;
	private $log;
	public  $stid;
	public  $osp_params;
	public  $isp_params;
	public $refcur;
	
	/**
	 * @param  array $options 查询语句
	 * @return 查询返回结果，查询失败返回为NULL
	 */
	function __construct($options) {
		$this -> debug = $options['debug'];
		$this -> log = $options['log'];
		$cnn = $this -> connectDB($options['user'], $options['password'], $options['oTNS']);
		if(!$cnn) {
			return false;
		}
	}
	public function __destruct(){
		//oci_free_statement($this->refcur);
		if(is_resource($this->refcur)){
		oci_free_statement($this->refcur);
		}
		if(is_resource($this->stid)){
			//释放资源
			oci_free_statement($this->stid);
		}
		
		if($this -> isp_params){
			//释放缓冲区
			foreach ($this -> isp_params as &$is_param){
				$is_param->free();
			}
		}
		if (is_resource ( $this->conn_handle )) {
			//关闭数据库连接
			@oci_close ( $this->conn_handle );
		}
		
	}
		/**
	 * 连接数据库
	 * @param	string	$usee	用户名
	 * @param  	string	$pass 	密码
	 * @param  	string	$db 	数据库连接配置TNS
	 * @param  	string	$mode 	数据库连接方式
	 *
	 * @return 查询返回结果，查询失败返回为NULL
	 */
	public function connectDB($user = '', $pass = '', $db = '') {
		try {
			$this -> conn_handle = @oci_new_connect($user, $pass, $db, 'UTF8');
			if(!is_resource($this -> conn_handle)) {
				throw new Exception('数据库连接错误!');
			}return true;
		} catch (Exception $e) {
			$this -> db_error(false, __FILE__, __LINE__, $e -> getMessage());
		}
	}
	/*****************************************************************************
	 * 与存储过程相关的操作开始
	******************************************************************************/
	/*查询单行记录*/
	public function queryDB($sp_name, &$sp_params) {
		try{
			//构建执行存储过程SQL语句
			array_push($sp_params, array("1",'o_dataset', '', -1,'OCI_B_CURSOR'), array("1",'o_errid', '', 64,''));
			return $this->execSPNew($sp_name, $sp_params);
		}catch (Exception $e){
			$this->setErrorMess($e->getMessage(),$sp_name,$sp_params);
		}
	}
	/*接口查询单行记录*/
	public function queryInterFaceDB($sp_name, &$sp_params) {
		try{
			array_push($sp_params, array("1",'o_errid', '', 64,''), array("1",'o_dataset', '', -1,'OCI_B_CURSOR'));
			return $this->execSPNew($sp_name, $sp_params);
		}catch (Exception $e){
			$this->setErrorMess($e->getMessage(),$sp_name,$sp_params);
		}
	}
	public function queryListInterFaceDB($sp_name, &$sp_params){
		try{
			array_push($sp_params, array("1",'o_errid', '', 64,''), array("1",'o_dataset', '', -1,'OCI_B_CURSOR'),array("1",'o_cnt', '', 64,''));
			$r =  $this->execSPNew($sp_name, $sp_params,true);
			$rs['count'] = $this->osp_params['o_cnt'];
			$rs['o_error_id'] = $this->osp_params['o_errid'];
			$rs['result'] = $r;
			return $rs;
		}catch (Exception $e){
			$this->setErrorMess($e->getMessage(),$sp_name,$sp_params);
		}
	}
	/*查询多行记录*/
	public function queryListDB($sp_name, &$sp_params) {
		try{
			//构建执行存储过程SQL语句
			array_push($sp_params, array("1",'o_dataset', '', -1,'OCI_B_CURSOR'),
			array("1",'o_count', '', 64,''),
			array("1",'o_errid', '', 64,''));
			$r =  $this->execSPNew($sp_name, $sp_params,true);
			$r['count'] = $this->osp_params['o_count'];
			$r['o_error_id'] = $this->osp_params['o_errid'];
			return $r;
		}catch (Exception $e){
			$this->setErrorMess($e->getMessage(),$sp_name,$sp_params);
		}
	}
	/*执行存储过程*/
	public function execSPNew($sp_name, &$sp_params = array(),$fatchall=false) {
		try{
			//构建执行存储过程SQL语句
			$stmt = $this -> getStmt($sp_name, &$sp_params);
			//绑定参数
			$this -> bindParams($stmt, $sp_params);
			//执行
			$r = @oci_execute($this -> stid);
			if(!$r) {
				throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
			} else {
				if(is_resource($this->refcur)){
					if(!oci_execute($this->refcur, OCI_DEFAULT)){
						throw new Exception($this->getErrorMess($this -> refcur, __FILE__, __LINE__));
					}
					//获取游标
					if(!$fatchall){
					$r = @oci_fetch_assoc($this->refcur);
					}else{
						oci_fetch_all($this->refcur, $r, null, null, OCI_FETCHSTATEMENT_BY_ROW);
					}
					//oci_free_statement($this->refcur);
					if(is_resource($this->refcur)){
					oci_free_statement($this->refcur);
					}
					return ($r);
				}else{
					return $this -> osp_params; // $err_id.$o_mbl_id;
				}
			}
		}catch (Exception $e){
			$this->setErrorMess($e->getMessage(),$sp_name,$sp_params);
		}
	}
	
	/*****************************************************************************
	 * 与存储过程相关的操作结束
	******************************************************************************/

	
	/*成生存储过程sql语句*/
	public function getStmt($sp_name, &$sp_params) {
		if(is_array($sp_params) && !empty($sp_params)) {
			foreach($sp_params as $param) {
				//获取输入参数名
				$params[] = ':' . $param[1];
			}
			//默认输出参数名
			$params = implode(",", $params);
			$stmt = "begin $sp_name($params); end;";
		} else {
			$stmt = "begin $sp_name; end;";
		}
		return $stmt;

	}
	/*绑定输入参数*/
	public function bindInputs($param_type,$param_name,$param_value,$out_value,$param_size){
		if(empty($param_type)) {
			if(!$r =@oci_bind_by_name($this -> stid, $param_name, $param_value,-1,SQLT_CHR)){
				throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
			}
		} else {
			//如果是blob或clob类型，调用方式不同
			if($param_type == 'OCI_B_BLOB'){
				$this -> isp_params["$out_value"] = @oci_new_descriptor($this->conn_handle, OCI_D_LOB);
				if(!$this->isp_params["$out_value"]){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
				if(!$r = @oci_bind_by_name($this -> stid, "$param_name", &$this -> isp_params["$out_value"], -1, OCI_B_BLOB)){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
				if(!($this -> isp_params["$out_value"]-> WriteTemporary($param_value,OCI_TEMP_BLOB))){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
			}elseif($param_type == 'OCI_B_CLOB'){
				$this -> isp_params["$out_value"] = @oci_new_descriptor($this->conn_handle, OCI_D_LOB);
				if(!$this->isp_params["$out_value"]){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
				if(!$r = @oci_bind_by_name($this -> stid, "$param_name", $this -> isp_params["$out_value"], -1, OCI_B_CLOB)){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
				if(!$this -> isp_params["$out_value"]-> WriteTemporary($param_value,OCI_TEMP_CLOB)){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
			}else{
				if(!$r = @oci_bind_by_name($this -> stid, $param_name, $param_value, $param_size, $param_type)){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
			}
		}
		return $r;
	}
	/*绑定输出参数*/
	public function bindOutputs($param_type,$param_name,$param_value,$out_value,$param_size){
		if(empty($param_type)) {
			if(!$r = @oci_bind_by_name($this -> stid, "$param_name", $this -> osp_params["$out_value"],$param_size,SQLT_CHR)){
				throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
			}
		} else {
			if($param_type == 'OCI_B_BLOB'){
				$this -> osp_params["$out_value"] = @oci_new_descriptor($this->conn_handle, OCI_D_LOB);
				if(!$this->osp_params["$out_value"]){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
				if(!$r = @oci_bind_by_name($this -> stid, "$param_name", $this -> osp_params["$out_value"], -1, OCI_B_BLOB)){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
			}elseif($param_type == 'OCI_B_CLOB'){
				$this -> osp_params["$out_value"] = oci_new_descriptor($this->conn_handle, OCI_D_LOB);
				if(!$this->osp_params["$out_value"]){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
				if(!$r = @oci_bind_by_name($this -> stid, "$param_name", $this -> osp_params["$out_value"], -1, OCI_B_CLOB)){
					throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
			}elseif($param_type == 'OCI_B_CURSOR'){
				if(!$this->refcur = @oci_new_cursor($this -> conn_handle)){
					throw new Exception($this->getErrorMess($this->stid,__FILE__,__LINE__));
				}
				if(!$r = @oci_bind_by_name($this -> stid, "$param_name", $this->refcur, -1, OCI_B_CURSOR)){
					throw new Exception($this->getErrorMess($this->stid,__FILE__,__LINE__));
				}
			}else
			if(!$r = @oci_bind_by_name($this -> stid, "$param_name", $this -> osp_params["$out_value"], $param_size, $param_type)){
				throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
			}
		}
		return $r;
	}
	/*生成资源*/
	public function setStid($stmt){
		$this -> stid = @oci_parse($this -> conn_handle, $stmt);
		if(!$this -> stid) {
			throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
		}else{
			return true;
		}
	}
/*绑定参数*/
	public function bindParams($stmt, $sp_params) {
		if(!$this->setStid($stmt)){
			throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
		}
		$this -> osp_params = array();
		if(!empty($sp_params)) {
			foreach($sp_params as $param) {
				$param_flag = $param[0];
				$param_name = ":$param[1]";
				$param_value = $param[2];
				$out_value = "$param[1]";
				$param_size = $param[3];
				$param_type = isset($param[4])? $param[4] :'';			
				//input param
				
				if($param_flag < 0) {
					$r = $this->bindInputs($param_type,$param_name,$param_value,$out_value,$param_size);	
				}
				//output param
				if($param_flag > 0) {
					$r = $this->bindOutputs($param_type,$param_name,$param_value,$out_value,$param_size);	
				}
				if(!$r) {
						throw new Exception($this->getErrormess($this->stid,__FILE__,__LINE__));
				}
				unset($param_name, $param_value, $param_size, $param_type,$inputLob);
			}
		}
	}
	

	/********************SQL 调用,直接操作sql,与存储过程无关********************/
	/**
	 * 取查询结果第一行的第$offset个字段
	 *
	 * @param string $sql 查询语句
	 * @param string $bindvars 查询语句
	 * @param int $offset 偏移
	 * @return 查询返回结果，查询失败返回为NULL
	 */
	function loadResult($sql, $bindvars, $offset = 0) {
		$r = $this -> query($sql, $bindvars);
		if(!$r) {
			$this -> db_error($this -> stid, __FILE__, __LINE__);
		}
		$r = oci_fetch_row($this -> stid);
		return isset($r[$offset]) ? $r[$offset] : null;
	}

	/*to object*/
	function loadResultObj($sql, $bindvars) {
		$r = $this -> query($sql, $bindvars);
		if(!$r) {
			$this -> db_error($this -> stid, __FILE__, __LINE__);
		}
		$r = oci_fetch_object($this -> stid);

		return ($r);
	}

	/**
	 * Assoc方式返回所有行
	 * @param string $sql
	 * @param string $bindvars 绑定参数
	 * return array 无结果返回空数组
	 */
	function loadAssocList($sql, $bindvars) {
		$r = $this -> query($sql, $bindvars);
		if(!$r) {
			$this -> db_error($this -> stid, __FILE__, __LINE__);
		}
		$r = oci_fetch_all($this -> stid, $results, null, null, OCI_FETCHSTATEMENT_BY_ROW);
		return ($results);
	}

	/**
	 * 返回一行
	 * @param string $sql
	 * @param string $bindvars 绑定参数
	 * return array，无结果集返回false
	 */
	function loadAssoc($sql, $bindvars) {
		//echo $sql;
		$r = $this -> query($sql, $bindvars);
		if(!$r) {
			$this -> db_error($this -> stid, __FILE__, __LINE__);
		}
		$results = oci_fetch_assoc($this -> stid);
		if(empty($results) || count($results) < 1)
		return false;
		else
		return $results;
	}

	/*查字段*/

	/**
	 * 更新一条
	 * @param string $table 表名
	 * @param string $array 更新数据值数组
	 * @param string $condition 主键,where 条件
	 * $condition = array('username'=>'周鹏')or $condition = "username='周鹏'"
	 * return bool   影响行数
	 */
	public function update($table, $array, $condition) {

		$sql = "update $table set ";
		if(!empty($array) && is_array($array)) {
			$i = 1;
			$bindargs = array();
			foreach($array as $key => $value) {
				$sql .= "$key = :$key";
				if($i < count($array)) {
					$sql .= ',';
				}
				$i++;
				array_push($bindargs, array("$key", "$value", -1));
			}
		}
		if(is_array($condition)) {
			$i = 1;
			foreach($condition as $k => $v) {
				$sql .= " where $k = :$k";
				if($i < count($condition)) {
					$sql .= " and ";
				}
				$i++;
				array_push($bindargs, array("$k", "$v", -1));
				//break;
			}
		} else {
			$sql .= " where $condition";
		}
		//echo $sql;
		//debug::show($bindargs);

		return $this -> exec($sql, $bindargs);
	}

	/*执行sql语句*/
	public function exec($sql, $bindvars) {
		$r = $this -> query($sql, $bindvars, null);
		if(!$r) {
			$this -> db_error($this -> stid, __FILE__, __LINE__);
		} else {

			return oci_num_rows($this -> stid);
		}

	}

	/**
	 * 更新一条
	 * @param string $table 表名
	 * @param string $table 更新数据值数组的数组
	 * @param string $table 主键,where 条件
	 * return bool   反正执行正确或失败
	 */

	/**
	 * 插入一条
	 * @param string $table 表名
	 * @param string $array 更新数据值数组
	 * @param string $condition 主键,where 条件
	 * $condition = array('username'=>'周鹏')or $condition = "username='周鹏'"
	 * return bool   影响行数
	 */
	public function insert($table, $array) {

		$sql = "insert into $table (";
		if(!empty($array) && is_array($array)) {
			$i = 1;
			$bindargs = array();
			$columns = '';
			$values = '';
			foreach($array as $key => $value) {
				$columns .= $key;
				$values .= ":$key";
				if($i < count($array)) {
					$columns .= ',';
					$values .= ',';
				}
				$i++;
				array_push($bindargs, array("$key", "$value", -1));
			}
			$sql .= $columns . ') values (' . $values . ')';
		}

		//echo $sql;
		//debug::show($bindargs);

		return $this -> exec($sql, $bindargs);

	}

	/**
	 * 插入一条
	 * @param string $table 表名
	 * @param string $table 插入数据值数组的数组
	 * return bool  失败返回false,成功返回true
	 */
	function insertList($table, $array) {
		$flag = true;
		if(is_array($array)) {
			foreach($array as $_array) {
				if(!$this -> insert($table, $_array)) {
					$flag = false;
				}
			}
		}
		return $flag;
	}

	/*删除一行*/
	public function delete($table, $condition) {
		$sql = "delete from $table where ";
		$bindargs = array();
		if(is_array($condition)) {
			$i = 1;
			foreach($condition as $k => $v) {
				$sql .= " $k = :$k";
				if($i < count($condition)) {
					$sql .= " and ";
				}
				$i++;
				array_push($bindargs, array("$k", "$v", -1));
				//break;
			}
		} else {
			$sql .= " where $condition";
		}
		return $this -> exec($sql, $bindargs);
	}

	/*清空一个表*/
	public function setEmpty($table) {
		$sql = "TRUNCATE TABLE  $table";
		if($this -> exec($sql) == 0) {
			return true;
		} else {
			return false;
		}
	}

	/*检查是否存在*/
	/**
	 * 执行SQL语句
	 * @param string $sql sql语句
	 * return bool  失败返回false,成功返回true
	 */
	function query($sql, $bindvars, $option = OCI_DEFAULT) {
		try {
			$this -> stid = oci_parse($this -> conn_handle, $sql);
			if(!$this -> stid) {
				throw new Exception('数据库处理出错！ ');
				//$this->db_error($conn, __FILE__, __LINE__,$e->getMessage());
			}
		} catch (Exception $e) {
			return $this -> db_error($conn, __FILE__, __LINE__, $e -> getMessage());
		}

		// Bind the PHP values to query bind parameters
		if(!empty($bindvars)) {
			foreach($bindvars as $b) {
				// create local variable with caller specified bind value
				$param_name = ":$b[0]";
				$param_value = $b[1];
				$param_size = $b[2];

				// oci_bind_by_name(resource, bv_name, php_variable, length)
				try {
					$r = oci_bind_by_name($this -> stid, $param_name, $param_value, $param_size);
					if(!$r) {
						throw new Exception('数据库处理出错！ ');
					}
				} catch (Exception $e) {
					return $this -> db_error($conn, __FILE__, __LINE__, $e -> getMessage());
				}
				unset($param_name, $param_value, $param_size);
				if(!$r) {
					$this -> db_error($this -> stid, __FILE__, __LINE__);
				}
			}
		}
		try {
			$rs = $option ? @oci_execute($this -> stid, $option) : @oci_execute($this -> stid);
			if(!$rs) {
				throw new Exception('数据库处理出错！ ');
			}
		} catch (Exception $e) {

			return $this -> db_error($this -> stid, __FILE__, __LINE__, $e -> getMessage());
		}
		return $rs;

	}

	function db_error($r = false, $file, $line, $m = 'Unknown DB error') {
		if($this -> debug){
			$err = $r ? oci_error($r) : oci_error();

			if(isset($err['message'])) {
				$m .= htmlentities($err['message']);
			}

			echo '<p><b>Error</b>: at line ' . $line . ' of ' . $file . '</p>';
			echo '<pre>' . $m . '</pre>';
		}
		if($this -> log) {

		}
		exit ;
	}
	public function getErrorMess($r, $file, $line){
		$erro = oci_error($r);
		$m = $erro['message'];
		$err = "<p><b>Error</b>: at line '  <b>$line</b>  ' of ' <b>$file</b>  '</p>".$m;
		return $err;
	}
	public function setErrorMess($m,$sp_name,$sp_params){
		$error = array('message'=>$m,'sp_name'=>$sp_name,'sp_params'=>$sp_params);
		if($this->log){
			logs::note($error,'orcle错误日志','orcle');
		}
		if($this->debug){
		debug::show($error);
		}
		return false;
		exit;
	}

}

?>